package com.kui.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;


public class DBHelp<T> {
	
	//创建数据库连接池
	private static BasicDataSource dbs = new BasicDataSource();
	
	static {
		Properties properties = new Properties();
		
		try {
			properties.load(DBHelp.class.getClassLoader().getResourceAsStream("dbconfig.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		String driver = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String user = properties.getProperty("username");
		String password = properties.getProperty("password");
		
		dbs.setDriverClassName(driver);
		dbs.setUrl(url);
		dbs.setUsername(user);
		dbs.setPassword(password);
		
		dbs.setInitialSize(5);
		dbs.setMaxWait(5000);
		dbs.setMaxActive(20);
		dbs.setMinIdle(10);
	}
	
	/**
	 * 获取连接
	 * @return
	 */
	public Connection getConnection() {
		try {
			return dbs.getConnection();
		}  catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 执行更新(插入、删除、修改)
	 * @param sql
	 * @param args
	 */
	public void executeSQL(String sql,Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			stat.executeUpdate();
			System.out.println("SQL:" + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stat,conn);
		}
	}
	
	/**
	 * 查找单一的对象
	 * @param sql
	 * @param mapper
	 * @param args
	 * @return 找到的对象，否则null
	 */
	public T executeQueryForObject(String sql,RowMapper<T> mapper,Object... args){
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		T t = null;
		
		try {
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			if(rs.next()) {
				t = mapper.mapperRow(rs);
			}
			System.out.println("SQL:" + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat,conn);
		}
		return t;
		
	}
	
	/**
	 * Count() 聚合函数
	 * @param sql
	 * @param args
	 * @return 数量
	 */
	public int executeQueryForCount(String sql,Object... args){
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		int t = 0;
		
		try {
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			if(rs.next()) {
				t = rs.getInt(1);
			}
			System.out.println("SQL:" + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat,conn);
		}
		return t;
		
	}
	
	/**
	 * 获取所需对象集合
	 * @param sql
	 * @param mapper
	 * @param args
	 * @return 对象集合
	 */
	public List<T> executeQueryForList(String sql,RowMapper<T> mapper,Object... args){
		Connection conn = getConnection();
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<T> list = new ArrayList<T>();
		
		try {
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			while(rs.next()) {
				T t = mapper.mapperRow(rs);
				list.add(t);
			}
			System.out.println("SQL:" + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs,stat,conn);
		}
		return list;
		
	}
	
	/**
	 * 获取自动生成的主键
	 * @param sql 插入的语句
	 * @param objs 条件
	 * @return 自动生成的主键，失败返回-1
	 */
	public int getAutoKey(String sql, Object...objs) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		int autoKey = -1;
		
		conn = getConnection();
		try {
			stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );
			
			for(int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			
			stat.executeUpdate();
			
			
			rs = stat.getGeneratedKeys();
			
			if(rs.next()) {
				autoKey = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		
		return autoKey;
	}
	
	/**
	 * 释放更新时的数据库资源
	 * @param stat
	 * @param conn
	 */
	public void close(PreparedStatement stat,Connection conn) {
		close(null,stat,conn);
	}
	
	/**
	 * 释放查询时的数据库资源
	 * @param rs
	 * @param stat
	 * @param conn
	 */
	public void close(ResultSet rs,PreparedStatement stat,Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stat != null) {
					stat.close();
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
}
